﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.Odbc;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using log4net;
using System.Windows.Forms;


namespace VehicleConfigurator.Database
{
    class clsDb
    {
        private static OdbcConnection OdbcCon;

        // private OdbcCommand OdbcCom;

        private OdbcDataReader OdbcDR;
        private DataTable dt;
        public OdbcDataAdapter da = new OdbcDataAdapter();
        private OdbcCommand OdbcCom;
        private BindingSource bindingSource1 = new BindingSource();

        private string ConStr;
        static readonly ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); //Constructing log

        public bool DBConn()
        {
            //ConStr = ConfigurationManager.ConnectionStrings["MysqlConnStr"].ConnectionString;
            //ConStr = "Driver={MySQL ODBC 5.1 Driver};uid=root; password=abc13579; Database=VehicleConfigurator2";
            ConStr = "Driver={MySQL ODBC 5.1 Driver};server=" + clsVariable.Server + ";uid=" + clsVariable.DBUserName + ";password=" +
                clsVariable.DBPass + ";database=" + clsVariable.DBName +";port=" + clsVariable.Port;

            OdbcCon = new OdbcConnection(ConStr);
            try
            {

                // Try openning the connection
                log.Debug("Openning connection...\r\n");

                // Make sure the connection is closed
                if (OdbcCon.State == ConnectionState.Closed)
                {
                    OdbcCon.Open();
                    log.Debug("Connection opened");
                    return true;
                }
            }

            catch (System.Data.Odbc.OdbcException myerror)
            {

                // An error occured, give details
                MessageBox.Show("Cannot connect to database: " + myerror.Message, "Error Connection", MessageBoxButtons.RetryCancel, MessageBoxIcon.Warning);
                log.Error("Cannot connect to database: " + myerror.Message);
                return false;
            }
            return true;
        }

        public DataTable LoadData(string sql, DataGridView dg)
        {
            try
            {
                bool a = this.DBConn();
                //MessageBox.Show(sql);
                OdbcCom = new OdbcCommand(sql, OdbcCon);
                // cmd.Connection
                da = new OdbcDataAdapter(OdbcCom);

                dt = new DataTable();
                dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
                da.Fill(dt);
                dg.DataSource = dt;
                if (OdbcCon.State == ConnectionState.Open)
                    OdbcCon.Close();
                return dt;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                throw ex;
            }
            finally
            {
                this.closeConnection();
            }
        }


        public int RunQuery(string sql)
        {
            int count = -1;
            OdbcTransaction transaction = null;
            try
            {
                this.DBConn();
                //OdbcCom = new OdbcCommand(sql, OdbcCon);
               // OdbcCom.ExecuteNonQuery();
                transaction = OdbcCon.BeginTransaction();
                OdbcCom = new OdbcCommand(sql, OdbcCon, transaction);
                count = OdbcCom.ExecuteNonQuery();

                transaction.Commit();
                return count;
            }
            catch (Exception ex)
            {
                try
                {
                    transaction.Rollback();
                }
                catch (Exception ez) 
                {
                    log.Error(ez.Message);
                    MessageBox.Show(ez.Message);
                }

                log.Error(ex.Message);
                MessageBox.Show(ex.Message);
                if (OdbcCon.State != ConnectionState.Closed)
                    OdbcCon.Close();
                return count;
               // throw ex;
            }
            finally
            {
                this.closeConnection();
            }
        }
        public void updateData(string table, DataGridView dg)
        {
            string sql = "SELECT * FROM " + table;
            da = new OdbcDataAdapter();
            OdbcCom = new OdbcCommand(sql, OdbcCon);
            da.SelectCommand = OdbcCom;

            OdbcCommandBuilder cmdbuilder = new OdbcCommandBuilder(da);
            this.DBConn();

            //dt = new DataTable();
            //da.Fill(dt);
            //dt = new DataTable();
            //da.Fill(dt);
            dt = (DataTable)dg.DataSource;


            try
            {
                da.Update(dt);
                if (OdbcCon.State == ConnectionState.Open)
                    OdbcCon.Close();
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                MessageBox.Show(ex.Message);
                if (OdbcCon.State == ConnectionState.Open)
                    OdbcCon.Close();
            }
            finally
            {
                this.closeConnection();
            }

        }

        public int UpdateDatatable(DataTable ds, string tableName)
        {
            var sql = "SELECT * FROM " + tableName;
            var da = new OdbcDataAdapter();
            try
            {
                OdbcCom = new OdbcCommand(sql, OdbcCon);
                da.SelectCommand = OdbcCom;
                var cmdbuilder = new OdbcCommandBuilder(da);
                cmdbuilder.ConflictOption = ConflictOption.OverwriteChanges;
                this.DBConn();
                return da.Update(ds);
            }
            catch (Exception ex)
            {
                log.Error(ex.Message, ex);
                throw ex;
            }
            finally
            {
                this.closeConnection();
            }
        }


        public int countRecord(string sql)
        {
            try
            {
                this.DBConn();
                OdbcCom = new OdbcCommand(sql, OdbcCon);
                int val = 0;
                object obj = OdbcCom.ExecuteScalar();

                val = (obj == null ? -1 : Convert.ToInt32(obj.ToString()));
                if (OdbcCon.State == ConnectionState.Open)
                    OdbcCon.Close();
                return val;
            }
            catch (Exception ex)
            {
                log.Debug(ex.Message, ex);
                throw ex;
            }
            finally
            {
                this.closeConnection();
            }
        }


        public DataTable GetDataTable(string sql)
        {
            try
            {
                bool a = this.DBConn();
                //MessageBox.Show(sql);
                OdbcCom = new OdbcCommand(sql, OdbcCon);
                // cmd.Connection
                da = new OdbcDataAdapter(OdbcCom);

                dt = new DataTable();
                dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
                da.Fill(dt);
                //if (OdbcCon.State == ConnectionState.Open)
                //    OdbcCon.Close();
                return dt;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                throw ex;
            }
            finally
            {
                this.closeConnection();
            }
        }


        private void closeConnection()
        {
            try
            {
                if (OdbcCon != null && OdbcCon.State != ConnectionState.Closed)
                {
                    OdbcCon.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.Message, ex);
            }
        }
        public DataTable excuteProc(string cmdText)
        {
            try
            {
                bool a = this.DBConn();
                //MessageBox.Show(sql);
                OdbcCom = new OdbcCommand();
                OdbcCom.CommandText = cmdText;//"call" + cmdText + "("+string.Empty+")";
                OdbcCom.CommandType = CommandType.StoredProcedure;
                OdbcCom.Connection = OdbcCon;

                //OdbcParameter par = new OdbcParameter("@sql",string.Empty);
                //par.Direction = ParameterDirection.Input;
                //OdbcCom.Parameters.Add(par);

                da = new OdbcDataAdapter(OdbcCom);

                dt = new DataTable();
                dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message, ex);
                throw ex;
            }
            finally
            {
                this.closeConnection();
            }
        }

        public int saveModel(string sql)
        {
            int count = -1;
            OdbcTransaction transaction = null;
            try
            {
                this.DBConn();

                // start the transaction

                transaction = OdbcCon.BeginTransaction();
                OdbcCom = new OdbcCommand();
                //OdbcCom.Transaction = transaction;

                OdbcCom = new OdbcCommand(sql, OdbcCon, transaction);
                OdbcCom.ExecuteNonQuery();

                OdbcCom.CommandText = "select last_insert_id();";
                count = Convert.ToInt32(OdbcCom.ExecuteScalar());

                transaction.Commit();
                return count;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                try
                {
                    // Attempt to roll back the transaction.
                    transaction.Rollback();
                    // return count;
                }
                catch
                {
                    // Do nothing here; transaction is not active.
                }
                //return count;
               throw ex;
            }
            finally
            {
                this.closeConnection();
            }

        }


    }
}
